import pymysql
import PySimpleGUI as sg


# 连接数据库
# db = pymysql.connect(host='localhost', database='bank', user='root', password='ch030309', charset='utf8')
# 创建数据库bank
# cursor.execute('create database bank charset utf8;')
# cursor.execute('use bank;')

# 预定义用户,连接数据库读取用户信息,用于后面登录操作
# 顾客登录信息
db = pymysql.connect(host='localhost', database='bank', user='root', password='ch030309', charset='utf8')
cursor = db.cursor(pymysql.cursors.DictCursor)
sql = "select cu_id as 账号 , cu_pd as 密码 from customer "
cursor.execute(sql)
customer_List = cursor.fetchall()
db.close()

# 服务员登录信息
db = pymysql.connect(host='localhost', database='bank', user='root', password='ch030309', charset='utf8')
cursor = db.cursor(pymysql.cursors.DictCursor)
sql = "select wa_id as 账号 , wa_pd as 密码 from waiter "
cursor.execute(sql)
waiter_List = cursor.fetchall()
db.close()

# 管理员登录信息
db = pymysql.connect(host='localhost', database='bank', user='root', password='ch030309', charset='utf8')
cursor = db.cursor(pymysql.cursors.DictCursor)
sql = "select ad_id as 账号 , ad_pd as 密码 from admin1 "
cursor.execute(sql)
admin_List = cursor.fetchall()
db.close()


# 登录系统
# 用户身份选择函数
def menu():
    layout = [[sg.B('管理员'), sg.B('顾客'), sg.B('服务员'),sg.B('退出系统')]]
    window = sg.Window('欢迎来的银行信息管理系统！', layout, font=("微软雅黑", 20))
    while True:
        event, values = window.read()
        if event is None or event == '退出系统':
            window.close()
            print('安全退出')
        break
    window.close()
    return event


# 登录函数
def logon(list, id):
    layout = [
        [sg.T('账号', size=6), sg.In('', font=20, key='user')],
        [sg.T('密码', size=6), sg.In('', font=20, key='pwd')],
        [sg.B('确认'), sg.B('取消')]
    ]
    window = sg.Window(id+'登录系统', layout, font=("微软雅黑", 20))
    while True:
        event, values = window.read()
        if event == "确认":
            for user in list:
                if values['user'] == user['账号'] and values['pwd'] == user['密码']:
                    a = "输入正确"
                    c = values['user']
                else:
                    b = "输入错误"
            try:
                if a == "输入正确":
                    sg.Popup(a)
                    break
            except NameError:
                sg.Popup(b)
        if event == sg.WINDOW_CLOSED or event == '取消':
            c = '取消'
            break
    window.close()
    return c


# 管理员界面
def adminint(id_a):
    db = pymysql.connect(host='localhost', database='bank', user='root', password='ch030309', charset='utf8')
    cursor = db.cursor(pymysql.cursors.DictCursor)
    sql = "select ad_id as 账号 from admin1 where ad_id='%s'"%(id_a)
    cursor.execute(sql)
    data1 = cursor.fetchone()
    sql = "select ad_name as 姓名 from admin1 where ad_id='%s'"%(id_a)
    cursor.execute(sql)
    data2 = cursor.fetchone()
    sql = "select ad_sex as 性别 from admin1 where ad_id='%s'"%(id_a)
    cursor.execute(sql)
    data3 = cursor.fetchone()
    sql = "select ad_phone as 电话 from admin1 where ad_id='%s'"%(id_a)
    cursor.execute(sql)
    data4 = cursor.fetchone()
    db.close()
    layout = [
        [sg.T(data1, size=(20, 3))],
        [sg.T(data2, size=(20, 3))],
        [sg.T(data3, size=(20, 3))],
        [sg.T(data4, size=(20, 3))],
        [sg.B('修改个人信息'), sg.B('增加用户')],
        [sg.B('查询记录')]
    ]
    window = sg.Window('管理员信息', layout, font=("微软雅黑", 20))
    while True:
        event, values = window.read()
        if event is None:
            window.close()
        else:
            window.close()
        break
    return event


# 管理员修改个人信息函数
def alter_ad(id_a):
    layout = [
        [sg.T('密码', size=6), sg.In('', font=20, key='ad_pd')],
        [sg.T('姓名', size=6), sg.In('', font=20, key='ad_name')],
        [sg.T('性别', size=6), sg.In('', font=20, key='ad_sex')],
        [sg.T('电话', size=6), sg.In('', font=20, key='ad_phone')],
        [sg.B('确定修改')]
    ]
    window = sg.Window('修改信息', layout, font=("微软雅黑", 20))
    while True:
        event, values = window.read()
        if event is None:
            window.close()
        else:
            db = pymysql.connect(host='localhost', database='bank', user='root', password='ch030309', charset='utf8')
            cursor = db.cursor()
            sql = "delete from admin1 where ad_id=%s"%(id_a)
            cursor.execute(sql)
            db.commit()
            sql = f"insert into admin1 values('{id_a}','{values['ad_pd']}','{values['ad_name']}','{values['ad_sex']}','{values['ad_phone']}')"
            cursor.execute(sql)
            db.commit()
            db.close()
            window.close()
            layout = [
            [sg.T('修改成功！请退出系统重新登陆！')],
            [sg.B('确定')]
            ]
            window = sg.Window('',layout, font=("微软雅黑", 20))
            while True:
                event, values = window.read()
                if event is None:
                    window.close()
                else:
                    window.close()
                break
        break


# 增加用户函数
def add_user():
    layout = [
        [sg.B('管理员'), sg.B('顾客'), sg.B('服务员')]
    ]
    window = sg.Window('请选择身份', layout, font=("微软雅黑", 20))
    while True:
        event, values = window.read()
        if event is None:
            window.close()
            print('安全退出')
        elif event == '管理员':
            layout = [
                [sg.T('账号', size=6), sg.In('', font=20, key='ad_id')],
                [sg.T('密码', size=6), sg.In('', font=20, key='ad_pd')],
                [sg.T('姓名', size=6), sg.In('', font=20, key='ad_name')],
                [sg.T('性别', size=6), sg.In('', font=20, key='ad_sex')],
                [sg.T('电话', size=6), sg.In('', font=20, key='ad_phone')],
                [sg.B('确定')]
            ]
            window = sg.Window('增加信息', layout, font=("微软雅黑", 20))
            while True:
                event, values = window.read()
                if event is None:
                    window.close()
                    print('安全退出')
                    break
                else:
                    db = pymysql.connect(host='localhost', database='bank', user='root', password='ch030309', charset='utf8')
                    cursor = db.cursor()
                    sql = f"insert into admin1 values('{values['ad_id']}','{values['ad_pd']}','{values['ad_name']}','{values['ad_sex']}','{values['ad_phone']}')"
                    cursor.execute(sql)
                    db.commit()
                    db.close()
                    window.close()
                    layout = [
                        [sg.T('增加成功！请退出系统重新登陆！')],
                        [sg.B('确定')]
                    ]
                    window = sg.Window('',layout, font=("微软雅黑", 20))
                    while True:
                        event, values = window.read()
                        if event is None:
                            window.close()
                        else:
                            window.close()
                        break
                break
            break
        elif event == '顾客':
            layout = [
                [sg.T('账号', size=6), sg.In('', font=20, key='cu_id')],
                [sg.T('密码', size=6), sg.In('', font=20, key='cu_pd')],
                [sg.T('姓名', size=6), sg.In('', font=20, key='cu_name')],
                [sg.T('性别', size=6), sg.In('', font=20, key='cu_sex')],
                [sg.T('电话', size=6), sg.In('', font=20, key='cu_phone')],
                [sg.T('余额', size=6), sg.In('', font=20, key='balance')],
                [sg.B('确定')]
            ]
            window = sg.Window('增加信息', layout, font=("微软雅黑", 20))
            while True:
                event, values = window.read()
                if event is None:
                    window.close()
                    print('安全退出')
                    break
                else:
                    db = pymysql.connect(host='localhost', database='bank', user='root', password='ch030309', charset='utf8')
                    cursor = db.cursor()
                    sql = f"insert into customer values('{values['cu_id']}','{values['cu_pd']}','{values['cu_name']}','{values['cu_sex']}','{values['cu_phone']}','{values['balance']}')"
                    cursor.execute(sql)
                    db.commit()
                    db.close()
                    window.close()
                    layout = [
                        [sg.T('增加成功！请退出系统重新登陆！')],
                        [sg.B('确定')]
                    ]
                    window = sg.Window('',layout, font=("微软雅黑", 20))
                    while True:
                        event, values = window.read()
                        if event is None:
                            window.close()
                        else:
                            window.close()
                        break
                break
            break
        else:
            layout = [
                [sg.T('账号', size=6), sg.In('', font=20, key='wa_id')],
                [sg.T('密码', size=6), sg.In('', font=20, key='wa_pd')],
                [sg.T('姓名', size=6), sg.In('', font=20, key='wa_name')],
                [sg.T('性别', size=6), sg.In('', font=20, key='wa_sex')],
                [sg.T('电话', size=6), sg.In('', font=20, key='wa_phone')],
                [sg.B('确定')]
            ]
            window = sg.Window('增加信息', layout, font=("微软雅黑", 20))
            while True:
                event, values = window.read()
                if event is None:
                    window.close()
                    print('安全退出')
                    break
                else:
                    db = pymysql.connect(host='localhost', database='bank', user='root', password='ch030309',
                                         charset='utf8')
                    cursor = db.cursor()
                    sql = f"insert into waiter values('{values['wa_id']}','{values['wa_pd']}','{values['wa_name']}','{values['wa_sex']}','{values['wa_phone']}')"
                    cursor.execute(sql)
                    db.commit()
                    db.close()
                    window.close()
                    layout = [
                        [sg.T('增加成功！请退出系统重新登陆！')],
                        [sg.B('确定')]
                    ]
                    window = sg.Window('', layout, font=("微软雅黑", 20))
                    while True:
                        event, values = window.read()
                        if event is None:
                            window.close()
                        else:
                            window.close()
                        break
                break
            break


def ad_query():
    layout = [
        [sg.T('记录编号', size=6), sg.In('', font=20, key='cre_id')],
        [sg.B('确认')]
    ]
    window = sg.Window('请输入记录编号', layout, font=("微软雅黑", 20))
    while True:
        event, values = window.read()
        if event is None:
            window.close()
            break
        elif event == '确认':
            a = values['cre_id']
            db = pymysql.connect(host='localhost', database='bank', user='root', password='ch030309', charset='utf8')
            cursor = db.cursor(pymysql.cursors.DictCursor)
            sql = "select cre_id as 记录编号 from ac_record where cre_id='%s'"%(a)
            cursor.execute(sql)
            data1 = cursor.fetchone()
            sql = "select cu_id as 顾客编号 from ac_record where cre_id='%s'"%(a)
            cursor.execute(sql)
            data2 = cursor.fetchone()
            sql = "select wa_id as 服务员编号 from ac_record where cre_id='%s'"%(a)
            cursor.execute(sql)
            data3 = cursor.fetchone()
            sql = "select state as 存取状态 from ac_record where cre_id='%s'" % (a)
            cursor.execute(sql)
            data4 = cursor.fetchone()
            sql = "select c_num as 金额 from ac_record where cre_id='%s'"%(a)
            cursor.execute(sql)
            data5 = cursor.fetchone()
            db.close()
            layout = [
                [sg.T(data1, size=(20, 3))],
                [sg.T(data2, size=(20, 3))],
                [sg.T(data3, size=(20, 3))],
                [sg.T(data4, size=(20, 3))],
                [sg.T(data5, size=(20, 3))],
                [sg.B('安全退出系统'),sg.B('继续')]
            ]
            window = sg.Window('存取记录', layout, font=("微软雅黑", 20))
            while True:
                event, values = window.read()
                if event is None:
                    window.close()
                    break
                elif event == '安全退出系统':
                    window.close()
                    break
                elif event == '继续':
                    ad_query()
        break


def customerint(id_a):
    db = pymysql.connect(host='localhost', database='bank', user='root', password='ch030309', charset='utf8')
    cursor = db.cursor(pymysql.cursors.DictCursor)
    sql = "select cu_id as 账号 from customer where cu_id='%s'"%(id_a)
    cursor.execute(sql)
    data1 = cursor.fetchone()
    sql = "select cu_name as 姓名 from customer where cu_id='%s'"%(id_a)
    cursor.execute(sql)
    data2 = cursor.fetchone()
    sql = "select cu_sex as 性别 from customer where cu_id='%s'"%(id_a)
    cursor.execute(sql)
    data3 = cursor.fetchone()
    sql = "select cu_phone as 电话 from customer where cu_id='%s'"%(id_a)
    cursor.execute(sql)
    data4 = cursor.fetchone()
    sql = "select balance as 余额 from customer where cu_id='%s'"%(id_a)
    cursor.execute(sql)
    data5 = cursor.fetchone()
    db.close()
    layout = [
        [sg.T(data1, size=(20, 3))],
        [sg.T(data2, size=(20, 3))],
        [sg.T(data3, size=(20, 3))],
        [sg.T(data4, size=(20, 3))],
        [sg.T(data5, size=(20, 3))],
        [sg.B('修改个人信息'), sg.B('查询记录')],
        [sg.B('存钱'), sg.B('取钱')]
    ]
    window = sg.Window('顾客个人信息', layout, font=("微软雅黑", 20))
    while True:
        event, values = window.read()
        if event is None:
            window.close()
        else:
            window.close()
        break
    return event


def alter_cu(id_a):
    layout = [
        [sg.T('密码', size=6), sg.In('', font=20, key='cu_pd')],
        [sg.T('姓名', size=6), sg.In('', font=20, key='cu_name')],
        [sg.T('性别', size=6), sg.In('', font=20, key='cu_sex')],
        [sg.T('电话', size=6), sg.In('', font=20, key='cu_phone')],
        [sg.B('确定修改')]
    ]
    window = sg.Window('修改信息', layout, font=("微软雅黑", 20))
    while True:
        event, values = window.read()
        if event is None:
            window.close()
        else:
            db = pymysql.connect(host='localhost', database='bank', user='root', password='ch030309', charset='utf8')
            cursor = db.cursor()
            sql = f"update customer set cu_pd='{values['cu_pd']}', cu_name='{values['cu_name']}',cu_sex='{values['cu_sex']}', cu_phone='{values['cu_phone']}' where cu_id='{id_a}'"
            cursor.execute(sql)
            db.commit()
            db.close()
            window.close()
            layout = [
                [sg.T('修改成功！请退出系统重新登陆！')],
                [sg.B('确定')]
            ]
            window = sg.Window('', layout, font=("微软雅黑", 20))
            while True:
                event, values = window.read()
                if event is None:
                    window.close()
                else:
                    window.close()
                break
        break


def cu_query(id_a):
    db = pymysql.connect(host='localhost', database='bank', user='root', password='ch030309', charset='utf8')
    cursor = db.cursor(pymysql.cursors.DictCursor)
    sql = "select cre_id as 编号, wa_id as 服务员编号, state as 存取状态, c_num as 金额 from ac_record where cu_id='%s'"%(id_a)
    cursor.execute(sql)
    data=cursor.fetchall()
    db.close()
    layout = [
        [sg.T(data)],
        [sg.B('安全退出系统')]
    ]
    window = sg.Window('查询结果', layout, font=("微软雅黑", 20))
    while True:
        event, values = window.read()
        if event is None:
            window.close()
        else:
            window.close()
        break


# 定义全局变量
num = 701
def save_money(id_a):
    layout = [
        [sg.T('服务员', size=6), sg.In('', font=20, key='wa_id')],
        [sg.T('金额', size=6), sg.In('', font=20, key='money')],
        [sg.B('确认'), sg.B('取消')]
    ]
    window = sg.Window('请选择服务员并输入金额，点取消退出系统', layout, font=("微软雅黑", 30))
    while True:
        event, values = window.read()
        values['money'] = int(values['money'])
        if event is None:
            window.close()
        elif event == '确认':
            db = pymysql.connect(host='localhost', database='bank', user='root', password='ch030309', charset='utf8')
            cursor = db.cursor(pymysql.cursors.DictCursor)
            sql = "update customer set balance= balance+%d where cu_id='%s'"%(values['money'],id_a)
            cursor.execute(sql)
            db.commit()
            global num
            num = num+1
            sql = f"insert into ac_record values('{num}','{id_a}','{values['wa_id']}','存',{values['money']})"
            cursor.execute(sql)
            db.commit()
            window.close()
            sql = "select balance as 当前余额 from customer where cu_id='%s'"%(id_a)
            cursor.execute(sql)
            data = cursor.fetchone()
            db.close()
            sg.Popup(data)
            print('安全退出系统')
        else:
            sg.Popup('即将退出程序')
            print('安全退出')
        break


def kill_money(id_a):
    db = pymysql.connect(host='localhost', database='bank', user='root', password='ch030309', charset='utf8')
    cursor = db.cursor(pymysql.cursors.DictCursor)
    sql = "select balance as 当前余额 from customer where cu_id='%s'" % (id_a)
    cursor.execute(sql)
    data = cursor.fetchone()
    layout = [
        [sg.T(data)],
        [sg.T('服务员', size=6), sg.In('', font=20, key='wa_id')],
        [sg.T('金额', size=6), sg.In('', font=20, key='money')],
        [sg.B('确认'), sg.B('取消')]
    ]
    window = sg.Window('请选择服务员并输入金额，不可超过余额，点取消退出系统', layout, font=("微软雅黑", 30))
    while True:
        event, values = window.read()
        values['money'] = int(values['money'])
        if event is None:
            window.close()
        elif event == '确认':
            db = pymysql.connect(host='localhost', database='bank', user='root', password='ch030309', charset='utf8')
            cursor = db.cursor(pymysql.cursors.DictCursor)
            sql = "update customer set balance= balance-%d where cu_id='%s'" % (values['money'], id_a)
            cursor.execute(sql)
            db.commit()
            global num
            num = num + 1
            sql = f"insert into ac_record values('{num}','{id_a}','{values['wa_id']}','取',{values['money']})"
            cursor.execute(sql)
            db.commit()
            window.close()
            sql = "select balance as 当前余额 from customer where cu_id='%s'" % (id_a)
            cursor.execute(sql)
            data = cursor.fetchone()
            db.close()
            sg.Popup(data)
            print('安全退出系统')
        else:
            sg.Popup('即将退出程序')
            print('安全退出')
        break


def waiterint(id_a):
    db = pymysql.connect(host='localhost', database='bank', user='root', password='ch030309', charset='utf8')
    cursor = db.cursor(pymysql.cursors.DictCursor)
    sql = "select wa_id as 账号 from waiter where wa_id='%s'" % (id_a)
    cursor.execute(sql)
    data1 = cursor.fetchone()
    sql = "select wa_name as 姓名 from waiter where wa_id='%s'" % (id_a)
    cursor.execute(sql)
    data2 = cursor.fetchone()
    sql = "select wa_sex as 性别 from waiter where wa_id='%s'" % (id_a)
    cursor.execute(sql)
    data3 = cursor.fetchone()
    sql = "select wa_phone as 电话 from waiter where wa_id='%s'" % (id_a)
    cursor.execute(sql)
    data4 = cursor.fetchone()
    db.close()
    layout = [
        [sg.T(data1, size=(20, 3))],
        [sg.T(data2, size=(20, 3))],
        [sg.T(data3, size=(20, 3))],
        [sg.T(data4, size=(20, 3))],
        [sg.B('修改个人信息'), sg.B('查询记录')]
    ]
    window = sg.Window('服务员信息', layout, font=("微软雅黑", 20))
    while True:
        event, values = window.read()
        if event is None:
            window.close()
        else:
            window.close()
        break
    return event


def alter_wa(id_a):
    layout = [
        [sg.T('密码', size=6), sg.In('', font=20, key='wa_pd')],
        [sg.T('姓名', size=6), sg.In('', font=20, key='wa_name')],
        [sg.T('性别', size=6), sg.In('', font=20, key='wa_sex')],
        [sg.T('电话', size=6), sg.In('', font=20, key='wa_phone')],
        [sg.B('确定修改')]
    ]
    window = sg.Window('修改信息', layout, font=("微软雅黑", 20))
    while True:
        event, values = window.read()
        if event is None:
            window.close()
        else:
            db = pymysql.connect(host='localhost', database='bank', user='root', password='ch030309', charset='utf8')
            cursor = db.cursor()
            print(id_a)
            sql = f"update waiter set wa_pd='{values['wa_pd']}',wa_name='{values['wa_name']}',wa_sex='{values['wa_sex']}',wa_phone='{values['wa_phone']}'where wa_id='{id_a}';"
            cursor.execute(sql)
            db.commit()
            db.close()
            window.close()
            layout = [
                [sg.T('修改成功！请退出系统重新登陆！')],
                [sg.B('确定')]
            ]
            window = sg.Window('', layout, font=("微软雅黑", 20))
            while True:
                event, values = window.read()
                if event is None:
                    window.close()
                else:
                    window.close()
                break
        break


def wa_query(id_a):
    db = pymysql.connect(host='localhost', database='bank', user='root', password='ch030309', charset='utf8')
    cursor = db.cursor(pymysql.cursors.DictCursor)
    sql = "select cre_id as 编号, cu_id as 服务员编号, state as 存取状态, c_num as 金额 from ac_record where wa_id='%s'" % (
        id_a)
    cursor.execute(sql)
    data = cursor.fetchall()
    db.close()
    layout = [
        [sg.T(data)],
        [sg.B('安全退出系统')]
    ]
    window = sg.Window('查询结果', layout, font=("微软雅黑", 20))
    while True:
        event, values = window.read()
        if event is None:
            window.close()
        else:
            window.close()
        break


event = menu()
# print(event)
if event == '管理员':
    list = admin_List
    id = '管理员'
    id_a = logon(list, id)
#   print(id_a)
    if id_a == '取消':
        print('安全退出')
    else:
        event = adminint(id_a)
    if event == '修改个人信息':
        alter_ad(id_a)
    elif event == '增加用户':
        add_user()
    elif event == '查询记录':
        ad_query()
#    elif event == '注销':
#        db = pymysql.connect(host='localhost', database='bank', user='root', password='ch030309', charset='utf8')
#        cursor = db.cursor()


elif event == '顾客':
    list = customer_List
    id = '顾客'
    id_a = logon(list, id)
#   print(id_a)
    if id_a == '取消':
        print('安全退出')
    else:
        event = customerint(id_a)
#       print(event)
    if event == '修改个人信息':
        alter_cu(id_a)
    elif event == '查询记录':
        cu_query(id_a)
    elif event == '存钱':
        save_money(id_a)
    elif event == '取钱':
        kill_money(id_a)

elif event == '服务员':
    list = waiter_List
    id = '服务员'
    id_a = logon(list, id)
    if id_a == '取消':
        print('安全退出')
    else:
        event = waiterint(id_a)
    if event == '修改个人信息':
        alter_wa(id_a)
    elif event == '查询记录':
        wa_query(id_a)
else:
    print('安全退出')

